In this notebook, we try to estimate the cost of a hypothetical EITC program to aid with rental housing payments. The aim is to supply a family with enough EITC to have them pay no more than 30% of their income for housing. Namely, the algorithm we implement is as follows:
Let $y_i$ denote the income of family $i$. Furthermore, suppose that family $i$ has characteristics married $s \in \{0,1\}$ and children $c \in \{0,\dots, 3\}$. Suppose $y_i$ is such that it qualifies family $i$ for $e_i = e(y_i,s,c)$ amount of EITC benefits. Finally, suppose that family $i$ lives in location $l$ and pays rent $r$ for a $b\in \{0,\dots,4\}$ bedroom housing unit. Then, under our scheme, family $i$ will receive additional EITC housing benefits $h$ of:
$$h = max(r_{l,b} - .3(y_i+e_i), 0 )$$We have 3 datasets containing the following information:
Two datasets, taxes and FMRs, are merged based on their CDSA codes but not all of the codes match. Namely, the tax data contains 381 metros but only 351 of them match to the FMR data. We are able to match an additional 5 CDSA codes for a total of 356 metros. However, there is no guarantee that the additional 5 metros match precisely with the metro of the Brookings data. For example, the FMR data contain a CDSA as 'Santa Barbara-Santa Maria-Goleta' while the tax has CDSA 'Santa Maria-Santa Barbara'. clearly, the FMR area is larger but in adding the 5 metros, we disregard this discrepancy.
We have a distribution of files incomes in each metro area. The distributions are binned in increments of \$5k up to \$40k and then are binned at \$10k increments. Since we only have the number of people who filed within each bin, we assume a uniform distribution of incomes in each bin and use the bin mean as the representative income for that bin. However, the highest income for which EITC benefits are given is \$52400k but we must rely on \$55k. The latter will overestimate the income and number of EITC recipients in that bin. One proposal to mediate that is to divide the bin \$50-\$60k in half and to use \$52500 as the median and half the number of \$50-\$60k as the filers.
We do not have any figures on marriage but need to differentiate between married and unmarried families. Consequently, we employ 1-50.2% -figure from some Christian Science article*- as the proportion of adults married. In doing so, we must assume that marriage rates across the income distribution, number of children, and metros do not vary.
Both FMR and EITC data are for year 2014 whereas the tax data is for year 2013. Thus, we assume that FMRs and EITC benefits do not differ drastically between 2013 and 2014.
We only have numbers aggregated based on children and based on income bins but not across both; therefore, to estimate the proportion of families with each child type in each income bin, we assume that the number of children a family has does not vary across the income distributions.
Consider the following variables:
The tax data contains two sets of numbers data: 1) the number $n_l = \sum_{c} n_{c,l}$ of EITC eligible people in each metro for each family type 2) the number $m_l = \sum_{b} m_{b,l}$ of families who filed for EITC in each income bracket. Note $n_l \geq m_l \quad \forall l$. To acquire the number of family types in each income bracket, we calculate
$$p_{l,c} = \frac{n_{l,c}}{n_{l}}$$
then compute
$$k_{c,l,b} = p_{l,c} \times m_{b,l}$$
Where $k_{c,l,b}$ is the number of families with children $c$ and in income bin $b$ in metro $l$. Of course, for this we must assume that the proportions among the eligible hold equally among those who actually file. Finally, let $T(p_{l,c})$ be the total cost calculated using eligible counts and let $T(p_{l,b})$ be that total cost calculated using bin proportions then $$T(p_{l,c}) \geq T(p_{l,b})$$
In [2]:
##Load modules and set data path:
import pandas as pd
import numpy as np
import numpy.ma as ma
import re
data_path = "C:/Users/SpiffyApple/Documents/USC/RaphaelBostic"
In [3]:
#################################################################
################### load tax data ###############################
#upload tax data
tx = pd.read_csv("/".join([data_path, "metro_TY13.csv"]))
#there is some weird formatting in the data due to excel -> fix:
tx['cbsa'] = tx.cbsa.apply(lambda x: re.sub("=|\"", "",x))
#numerous numerical column entries have commas -> fix:
tx.iloc[:,3:] = tx.iloc[:,3:].replace(",", "",regex=True).astype(np.int64)
#set the cbsa as the dataframe index:
tx.set_index('cbsa', inplace=True)
In [4]:
#################################################################
###################### Read EITC data ###########################
##parse the EITC data:
eitc = pd.ExcelFile("/".join([data_path, "EITC Calculator-2-14.xlsx"]))
sheets = eitc.sheet_names
eitc.close()
eitc_dict = pd.read_excel("/".join([data_path, "EITC Calculator-2-14.xlsx"]), sheetname = sheets[9:17], skiprows = 14 )
eitc = pd.concat(eitc_dict)
EITC data contain the amount of EITC benefits a family receives for a given income level. The goal here is to get a family's total income and what would be considered a fair share of income for housing. This is where we perform following aforementioned calculation:
$$\text{total income} = y_i + e_i(s,c)$$$$\text{housing share} = .3\times \text{ total income} $$
In [5]:
#################################################################
################### Process EITC data ###########################
eitc = eitc.iloc[:,[0,40]]
eitc.dropna(inplace=True)
eitc = eitc.loc[eitc[2014]>0,:]
eitc.reset_index(level=0, inplace=True)
eitc.reset_index(drop=True, inplace=True)
#eitc['num_kids'] = eitc.level_0.str.extract("(\d)", expand=False)
#eitc['married'] = eitc.level_0.str.contains("Married").astype(int)
#calculate fair share of income for housing
eitc['total_income'] = eitc['Nominal Earnings']+eitc[2014]
eitc['haus_share'] = eitc.total_income*.3
#remove "Nominal" from family description.
eitc.level_0.replace(", Nominal", "", regex=True, inplace=True)
In order to estimate relate FMR data to family types, we must map the number of bedrooms to the number of children and marriage. Here, we assume that marriage does not make a difference, thus, the mapping only varies on the number of children in the family. Presently, the following is implemented:
$$ \begin{array}{ccc} \text{num of children} & & \text{num of bedrooms} \\ 0 & \to & 1 \\ 1 & \vdots & 2 \\ 2 & & 2 \\ 3+ & \to & 3 \\ \end{array} $$
In [6]:
##Map FMR data to EITC data (Variable)
#assigned bedrooms to child counts
repl_dict ={'Married, 0 Kid':'fmr1', 'Married, 1 Kid':'fmr2', 'Married, 2 Kids':'fmr2',
'Married, 3 Kids':'fmr3', 'Single, 0 Kid':'fmr1', 'Single, 1 Kid':'fmr2',
'Single, 2 Kids':'fmr2', 'Single, 3 Kids':'fmr3'}
eitc['r_type'] = eitc.level_0.replace(repl_dict)
haus_share = eitc[['level_0', 'haus_share', 'r_type', 'Nominal Earnings']]
In [7]:
#################################################################
################# Read & process fmr data ######################
#read in fmr data
fmr = pd.read_excel("/".join([data_path, "FY2014_4050_RevFinal.xls"]))
#drop non Metro areas:
fmr = fmr[fmr.Metro_code.str.contains("METRO")]
#extract cbsa code:
fmr['cbsa'] = fmr.Metro_code.str.extract("O(\d{4,5})[MN]", expand=False)
#edit FMR CBSA codes to match tax CBSA codes:
cbsa_chng_map = {'14060':'14010', '29140':'29200', '31100':'31080', '42060':'42200', '44600':'48260'}
fmr.cbsa.replace(cbsa_chng_map, inplace=True)
#drop duplicates based on cbsa code:
#fmr = fmr.drop_duplicates(['cbsa','Areaname'])
fmr = fmr.drop_duplicates('cbsa')
fmr.reset_index(drop=True, inplace=True)
#clean up the area names by removing "MSA and HUD Metro FMR Area"
fmr['Areaname'] = fmr.Areaname.apply(lambda x: re.sub(" MSA| HUD Metro FMR Area", "", x))
#set an interpratable index
fmr.set_index("cbsa", inplace=True)
#fetch columns that pertain to FMR figures
fmr_cols = fmr.columns[fmr.columns.str.contains("fmr\d")]
#reformat monthly fmr to annual cost of rent
fmr[fmr_cols] = fmr[fmr_cols]*12
#subset to only matching cbsa codes between tax and fmr data
common_cbsa = fmr.index.intersection(tx.index)
fmr = fmr.loc[common_cbsa]
tx = tx.loc[common_cbsa]
print("The number of CBSAs between tax and fmr data matches?:", fmr.shape[0] == tx.shape[0])
In [8]:
######################################
##0. Define function to calculate eitc
######################################
def calc_haus_eitc(group, income):
"""
INPUT:
1. group - (pd.df) subset of data frame by family type
2. income - (int64) total income the family earns
OUTPUT:
1. aid - (pd.Series) a series containing eitc housing aid for each family type for a given income
DESCRIPTION:
The function is basically the max(r - (y+e)*.3, 0) but if we are at income levels that don't qualify
a family type for EITC benefits then we need to output a corresponding vector of NAN values so that
the groupby operation doesn't error out on its concatenation step.
"""
details = group[group['Nominal Earnings'] == income]
if details.shape[0] > 0:
aid = fmr[details.r_type]-details.haus_share.iloc[0]
aid[aid<0] = 0
else:
aid = pd.DataFrame(np.array([np.nan]*fmr.shape[0]))
aid.index = fmr.index
#aid.columns = [group.r_type.iloc[0]]
aid.columns = ['aid']
return(aid)
In [9]:
######################################
##I. Make a vector of income bin means
######################################
min_earn = 2500
mid_earn = 37500
step = 5000
income_vect = np.linspace(min_earn,mid_earn,((mid_earn-min_earn)/step+1))
add_vect = [45000,52500]
income_vect = np.concatenate([income_vect, add_vect])
In [54]:
################################################
##II. Group by family type and loop over incomes
################################################
groups = haus_share.groupby(by = 'level_0')
#place eachincome is placed into a dictionary entry
aid_incomes = {}
for income in income_vect:
aid_per_income = groups.apply(calc_haus_eitc, income=income)
aid_incomes[income] = aid_per_income.unstack(level=0)
#concatenate dictionary into a 2-indexed data frame (flattened 3D)
one_family_aid = pd.concat(aid_incomes)
one_family_aid.columns = one_family_aid.columns.levels[1]
In [11]:
#################################################################
############### 0. pre-checks and params ########################
#set proportion of ppl married
prop_married = 1-50.2/100 #some Christian Science article (not sure if credible source)
#for convenience, extract cols corresponding with number of ppl filed for EITC in each income bracket
eagi_cols = tx.columns[tx.columns.str.contains("EAGI")]
#cut the 50-60 bin number in half according to assumptions
tx['EAGI50_13'] = tx['EAGI50_13']/2
#it doesn't seem that the total eligible for eitc matches the distributional count of incomes
print("Prop accounted for in income distribution counts\n",(tx[eagi_cols].sum(axis=1)/tx.eitc13).quantile(np.linspace(0,1,5)))
In [12]:
#################################################################
################ I. compute proportions #########################
eqc_cols = tx.columns[tx.columns.str.contains("EQC\d_")]
chld_prop = tx[eqc_cols].div(tx.eitc13,axis=0)
m_chld_prop = chld_prop*prop_married
s_chld_prop = chld_prop - m_chld_prop
m_chld_prop.columns = m_chld_prop.columns + "_married"
s_chld_prop.columns = s_chld_prop.columns + "_single"
tx = pd.concat([tx, m_chld_prop,s_chld_prop],axis=1)
eqc_cols = tx.columns[tx.columns.str.contains('EQC\d_13_married|EQC\d_13_single', regex=True)]
In [13]:
#################################################################
############### II. multiply to across ##########################
#here I make a 3D matrix with metros, bins, types on each axis
#then flatten it into a 2D data frame.
#Implicit broadcasting across two 2D matrices into a 3D matrix
C_3D=np.einsum('ij,ik->jik',tx[eagi_cols],tx[eqc_cols])
#flatten into a pandas dataframe
C_2D=pd.Panel(np.rollaxis(C_3D,2)).to_frame()
C_2D.columns = one_family_aid.columns
C_2D.index = one_family_aid.index
In [14]:
##################################################################
############### aggregate aid and filers #########################
disaggregated =np.multiply(C_2D, one_family_aid)
#summing once gives us metro-level totals -> summing that gives us total
total = disaggregated.sum(axis=1).sum()
In [15]:
print("Total EITC Housing Aid cost: %.2f billion" %(total/1e9))
In [16]:
C_3D.shape
Out[16]:
In [17]:
quantiles = np.divide(C_3D,np.cumsum(C_3D, axis=0))
In [18]:
#find the median income values
"""
Cool stuff: in essence, we have the following algorithm
-consider only values greater than .5 in the cumulative percentiles of each income bin
-find the minimum on the masked array -> these are the medians
-use the rows set as true as the identifiers of the median income in the index
"""
masked_quantiles = ma.masked_where(quantiles<=.5,quantiles)
median_idx = masked_quantiles.argmin(0)
median_idx = np.where(masked_quantiles == masked_quantiles.min(axis=0))
median_mat = np.zeros(quantiles.shape, dtype=bool)
median_mat[median_idx] = True
In [19]:
median_mat.shape
Out[19]:
In [20]:
#flatten into a pandas dataframe
median_2D=pd.Panel(np.rollaxis(median_mat,2)).to_frame()
median_2D.columns = one_family_aid.columns
median_2D.index = one_family_aid.index
In [21]:
#just double check the results from above
np.divide(tx[eagi_cols].cumsum(axis=1),tx[eagi_cols].sum(axis=1).reshape(357,1)).head()
Out[21]:
In [37]:
#the amount of aid the median income family in each metro is getting
median_metro_aid = one_family_aid[median_2D].reset_index(level=0, drop=True).dropna()
In [72]:
#the amount of aid the median income family in each metro is getting
median_metro_aid = one_family_aid[median_2D].reset_index(level=0, drop=True).dropna()
comparison = pd.concat([median_metro_aid]*10)
#inefficient way of making the comparison matrix the same size as the original matrix
comparison.index = one_family_aid.index
#also inefficient but copy in order to not lose the original matrix
X = one_family_aid.copy()
#set the values that are above the median to np.nan
X[comparison <= one_family_aid] = np.nan
#fill nan values with the median values
X.fillna(comparison, inplace=True)
In [70]:
##################################################################
############### aggregate aid and filers #########################
disaggregated =np.multiply(C_2D, X)
#summing once gives us metro-level totals -> summing that gives us total
total = disaggregated.sum(axis=1).sum()
In [71]:
print("Total EITC Housing Aid cost: %.2f billion" %(total/1e9))
In [ ]:
In [57]:
#Cacl number of metros where family earning ...
max_inc_aid = (one_family_aid.loc[52000]>0).sum(axis=1).sum()
print("Number of metros where family earning 52400 gets EITC housing aid: %d" %max_inc_aid)
expens_cbsas = one_family_aid.loc[52000,'Married, 3 Kids'].loc[(one_family_aid.loc[52000, "Married, 3 Kids"]>0)].index
In [60]:
fmr.loc[expens_cbsas].Areaname
Out[60]:
In [ ]:
masked_quantiles = ma.masked_where(quantiles<.5,quantiles)
median_idx = np.where(masked_quantiles == masked_quantiles.min(axis=0))
median_mat = np.zeros(quantiles.shape, dtype=bool)